﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC11BO: Dat tour
    /// </summary>
    public class VYC11BO:DBConnectionBO
    {
        public VYC11BO():base()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        /// <summary>
        /// Lay danh sach cac du lieu trong VYC00
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC11> GetAllList()
        {
            var list = new List<ModelVYC11>();
            const string query = "SELECT [Id],[IdTour],[IdKhachHang],[HoTen],[Email],[IdHinhThucThanhToan],[SDT],[CMND],[MST],[DiaChi],[SoLuong],[TenCongTy],[DiDong],[ChuThich],[NgayCapNhat],[TrangThai] FROM [VYC-11]";
            var sqlCommand = new SqlCommand(query, connection);
            var reader = sqlCommand.ExecuteReader();
            CloseConnection();
            while (reader.Read())
            {
                var model = new ModelVYC11();

                model.Id = reader["Id"].ToString();
                model.IdTour = reader["IdTour"].ToString();
                model.IdKhachHang = Convert.ToInt32(reader["IdKhachHang"]);
                model.HoTen = reader["HoTen"].ToString();
                model.Email = reader["Email"].ToString();
                model.IdHinhThucThanhToan = Convert.ToInt32(reader["IdHinhThucThanhToan"]);
                model.SDT = reader["SDT"].ToString();
                model.CMND = reader["CMND"].ToString();
                model.MST = reader["MST"].ToString();
                model.DiaChi = reader["DiaChi"].ToString();
                model.SoLuong = Convert.ToInt32(reader["SoLuong"]);
                model.TenCongTy = reader["TenCongTy"].ToString();
                model.DiDong = reader["DiDong"].ToString();
                model.ChuThich = reader["ChuThich"].ToString();
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);
                

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC11 model)
        {
            var query = "INSERT INTO [VYC-11]([Id],[IdTour],[IdKhachHang],[HoTen],[Email],[IdHinhThucThanhToan],[SDT],[CMND],[MST],[DiaChi],[SoLuong],[TenCongTy],[DiDong],[ChuThich],[NgayCapNhat],[TrangThai]) " +
                        "VALUES (@id, @idtour, @idkhachhang, @hoten, @email, @idhinhthucthanhtoan, @sdt, @cmnd, @mst, @diachi, @soluong" +
                        ", @tencongty, @didong, @chuthich ,GETDATE(), @trangthai)";

            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = model.Id;
            sqlCommand.Parameters.Add("@idtour", SqlDbType.VarChar).Value = model.IdTour;
            sqlCommand.Parameters.Add("@idkhachhang", SqlDbType.Int).Value = model.IdKhachHang;
            sqlCommand.Parameters.Add("@hoten", SqlDbType.NVarChar).Value = model.HoTen;
            sqlCommand.Parameters.Add("@email", SqlDbType.VarChar).Value = model.Email;
            sqlCommand.Parameters.Add("@idhinhthucthanhtoan", SqlDbType.Int).Value = model.IdHinhThucThanhToan;
            sqlCommand.Parameters.Add("@sdt", SqlDbType.VarChar).Value = model.SDT;
            sqlCommand.Parameters.Add("@cmnd", SqlDbType.VarChar).Value = model.CMND;
            sqlCommand.Parameters.Add("@mst", SqlDbType.VarChar).Value = model.MST;
            sqlCommand.Parameters.Add("@diachi", SqlDbType.NVarChar).Value = model.DiaChi;
            sqlCommand.Parameters.Add("@soluong", SqlDbType.Int).Value = model.SoLuong;
            sqlCommand.Parameters.Add("@tencongty", SqlDbType.NVarChar).Value = model.TenCongTy;
            sqlCommand.Parameters.Add("@didong", SqlDbType.VarChar).Value = model.DiDong;
            sqlCommand.Parameters.Add("@chuthich", SqlDbType.NVarChar).Value = model.ChuThich;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;

            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;                
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC11 model)
        {
            var query = "UPDATE [VYC-11] SET " +
                        "[IdTour]= @idtour" +
                        ",[IdKhachHang]= @idkhachhang" +
                        ",[HoTen]= @hoten" +
                        ",[Email]= @email" +
                        ",[IdHinhThucThanhToan]= @idhinhthucthanhtoan" +
                        ",[SDT]= @sdt"+
                        ",[CMND]= @cmnd"+
                        ",[MST]= @mst"+
                        ",[DiaChi]= @diachi"+
                        ",[SoLuong]= @soluong " +
                        ",[TenCongTy]= @tencongty" +
                        ",[DiDong]= @didong" +
                        ",[ChuThich]= @chuthich" +
                        ",[NgayCapNhat] = GETDATE(), [TrangThai] = @trangthai"+
                        " WHERE [Id] like @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = model.Id;
            sqlCommand.Parameters.Add("@idtour", SqlDbType.VarChar).Value = model.IdTour;
            sqlCommand.Parameters.Add("@idkhachhang", SqlDbType.Int).Value = model.IdKhachHang;
            sqlCommand.Parameters.Add("@hoten", SqlDbType.NVarChar).Value = model.HoTen;
            sqlCommand.Parameters.Add("@email", SqlDbType.VarChar).Value = model.Email;
            sqlCommand.Parameters.Add("@idhinhthucthanhtoan", SqlDbType.Int).Value = model.IdHinhThucThanhToan;
            sqlCommand.Parameters.Add("@sdt", SqlDbType.VarChar).Value = model.SDT;
            sqlCommand.Parameters.Add("@cmnd", SqlDbType.VarChar).Value = model.CMND;
            sqlCommand.Parameters.Add("@mst", SqlDbType.VarChar).Value = model.MST;
            sqlCommand.Parameters.Add("@diachi", SqlDbType.NVarChar).Value = model.DiaChi;
            sqlCommand.Parameters.Add("@soluong", SqlDbType.Int).Value = model.SoLuong;
            sqlCommand.Parameters.Add("@tencongty", SqlDbType.NVarChar).Value = model.TenCongTy;
            sqlCommand.Parameters.Add("@didong", SqlDbType.VarChar).Value = model.DiDong;
            sqlCommand.Parameters.Add("@chuthich", SqlDbType.NVarChar).Value = model.ChuThich;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;                
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC10 model)
        {
            var query = "DELETE FROM [VYC-11] " +
                        " WHERE Id like @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;                
            }
        }

        /// <summary>
        /// Get by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ModelVYC11> GetById(string id)
        {
            var list = new List<ModelVYC11>();
            var query = "SELECT [Id],[IdTour],[IdKhachHang],[HoTen],[Email],[IdHinhThucThanhToan],[SDT],[CMND],[MST],[DiaChi],[SoLuong],[TenCongTy],[DiDong],[ChuThich],[NgayCapNhat],[TrangThai] FROM [VYC-11] Where [Id] like @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.VarChar).Value = id;
            var reader = sqlCommand.ExecuteReader();
            CloseConnection();
            while (reader.Read())
            {
                var model = new ModelVYC11();

                model.Id = reader["Id"].ToString();
                model.IdTour = reader["IdTour"].ToString();
                model.IdKhachHang = Convert.ToInt32(reader["IdKhachHang"]);
                model.HoTen = reader["HoTen"].ToString();
                model.Email = reader["Email"].ToString();
                model.IdHinhThucThanhToan = Convert.ToInt32(reader["IdHinhThucThanhToan"]);
                model.SDT = reader["SDT"].ToString();
                model.CMND = reader["CMND"].ToString();
                model.MST = reader["MST"].ToString();
                model.DiaChi = reader["DiaChi"].ToString();
                model.SoLuong = Convert.ToInt32(reader["SoLuong"]);
                model.TenCongTy = reader["TenCongTy"].ToString();
                model.DiDong = reader["DiDong"].ToString();
                model.ChuThich = reader["ChuThich"].ToString();
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);

                list.Add(model);
            }
            return list;
            
        }
    }
}